package com.github.m6d21.sso.common;

import com.github.m6d21.sso.common.model.ExcelQaSchemaInfo;
import com.github.m6d21.sso.common.util.excel.ExcelHelper;
import java.io.Closeable;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.net.URL;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.junit.Test;

/**
 *<p>
 *
 *</p>
 *
 * @author liuyang
 * @date 2019/1/10 Thu 17:43:00
 * @since 1.0.0
 */
public class GenerateQaSchemaSql {

    private final String tableName = "qa_scheme_info";

    @Test
    public void testGenerateQaSchemaSql() {
        String excelUrl = "file:///Users/liuyang/Downloads/qa_scheme_info.xlsx";
        InputStream isp = null;
        try {
            URL url = new URL(excelUrl);
            isp = url.openConnection().getInputStream();
            ExcelHelper<ExcelQaSchemaInfo> helper = ExcelHelper.newInstance(ExcelQaSchemaInfo.class);
            List<ExcelQaSchemaInfo> excelQaSchemaInfoList = helper.parseExcel(isp);
            System.out.println("共有" + excelQaSchemaInfoList.size() + "条excel数据");
            List<String> sqlList = generateUpdateSql(excelQaSchemaInfoList);
            System.out.println("生成" + sqlList.size() + "条SQL");
            writeToFile(sqlList);
        } catch (IOException e) {
            System.out.println("读取文件失败");
        } finally {
            close(isp);
        }

    }

    private List<String> generateUpdateSql(List<ExcelQaSchemaInfo> excelQaSchemaInfoList) {
        if (CollectionUtils.isEmpty(excelQaSchemaInfoList)) {
            return new ArrayList<>(0);
        }

        List<String> sqlList = new ArrayList<>(4096);
        List<String> setPriceSqlList = new ArrayList<>(2048);
        List<String> setStatusSqlList = new ArrayList<>(1024);
        for (ExcelQaSchemaInfo info : excelQaSchemaInfoList) {
            boolean isSetPrice = false;
            String sql = null;
            if (info.getBasicPrice() != null && Integer.valueOf(info.getBasicPrice()) > 0) {
                isSetPrice = true;
                sql = generateUpdateBasicPriceSql(info);
            } else {
                sql = generateUpdateStatusSql(info);
            }
            if (StringUtils.isBlank(sql)) {
                continue;
            }
            if (isSetPrice) {
                setPriceSqlList.add(sql);
            } else {
                setStatusSqlList.add(sql);
            }
        }
        sqlList.addAll(setPriceSqlList);
        sqlList.addAll(setStatusSqlList);

        return sqlList;
    }


    private String generateUpdateStatusSql(ExcelQaSchemaInfo excelQaSchemaInfo) {
        if (excelQaSchemaInfo == null
            || StringUtils.isBlank(excelQaSchemaInfo.getId())
            || (StringUtils.isNotBlank(excelQaSchemaInfo.getBasicPrice())
            && Integer.valueOf(excelQaSchemaInfo.getBasicPrice()) > 0)
        ) {
            return null;
        }

        StringBuilder sql = new StringBuilder("update ");
        sql.append(wrapField(tableName)).append(" ");
        sql.append("set").append(" ");
        sql.append(wrapField("status")).append(" ").append(" = ")
            .append(wrapValue("N"));
        sql.append(" where ");
        sql.append(wrapField("id")).append(" = ").append(excelQaSchemaInfo.getId());
//        sql.append(appendCondition("brand_code", excelQaSchemaInfo.getBrandCode()));
//        sql.append(appendCondition("series_code", excelQaSchemaInfo.getSeriesCode()));
//        sql.append(appendCondition("model_code", excelQaSchemaInfo.getModelCode()));
        sql.append(appendCondition("qa_name", "臻新保"));
        sql.append(";");
        return sql.toString();
    }

    private String generateUpdateBasicPriceSql(ExcelQaSchemaInfo excelQaSchemaInfo) {
        if (excelQaSchemaInfo == null
            || StringUtils.isBlank(excelQaSchemaInfo.getBasicPrice())
            || StringUtils.isBlank(excelQaSchemaInfo.getId())
            || Integer.valueOf(excelQaSchemaInfo.getBasicPrice()) <= 0
//            || excelQaSchemaInfo.getBasicPrice().trim().equals(excelQaSchemaInfo.getNewBasicPrice().trim())
            || !isNumber(excelQaSchemaInfo.getBasicPrice())
        ) {
            return null;
        }

        StringBuilder sql = new StringBuilder("update ");
        sql.append(wrapField(tableName)).append(" ");
        sql.append("set").append(" ");
        sql.append(wrapField("basic_price")).append(" ").append(" = ")
            .append(wrapValue(excelQaSchemaInfo.getBasicPrice()));
        sql.append("where ");
        sql.append(wrapField("id")).append(" = ").append(excelQaSchemaInfo.getId());
//        sql.append(appendCondition("brand_code", excelQaSchemaInfo.getBrandCode()));
//        sql.append(appendCondition("series_code", excelQaSchemaInfo.getSeriesCode()));
//        sql.append(appendCondition("model_code", excelQaSchemaInfo.getModelCode()));
        sql.append(appendCondition("qa_name", "臻新保"));
        sql.append(";");
        return sql.toString();
    }

    private String wrapField(String s) {
        if (StringUtils.isBlank(s)) {
            return null;
        }
        return " `" + s + "` ";
    }

    private String wrapValue(String s) {
        if (StringUtils.isBlank(s)) {
            return null;
        }
        return " '" + s + "' ";
    }

    private String appendCondition(String filed, String value) {
        if (StringUtils.isBlank(value) || StringUtils.isBlank(filed)) {
            return "";
        }
        return " and " + wrapField(filed) + " = " + wrapValue(value);
    }

    private void writeToFile(List<String> strings) {
        if (CollectionUtils.isEmpty(strings)) {
            return;
        }
        String fileName = "qa_scheme_info";

        FileWriter fw = null;
        PrintWriter pw = null;
        int count = 0;
        File file = null;
        for (String s : strings) {
            try {
                if (count % 600 == 0) {
                    file = createFile(fileName + (count / 600) + ".sql");
                }
                count++;
                fw = new FileWriter(file, true);
                pw = new PrintWriter(fw);
//                if(count++%600==0){
//                    pw.println("#----"+count);
//                }
                pw.println(s);
            } catch (IOException e) {
                System.out.println("写入文件失败");
            } finally {
                close(fw);
                close(pw);
            }
        }
    }

    private void close(Closeable x) {
        if (x != null) {
            try {
                x.close();
            } catch (IOException e) {
                System.out.println("关闭失败");
            }
        }
    }

    private boolean isNumber(String s) {
        String regexp = "^[\\d]+[\\.]{0,1}[\\d]{0,}$";
        if (StringUtils.isBlank(s)) {
            return false;
        }
        Pattern pattern = Pattern.compile(regexp);
        Matcher matcher = pattern.matcher(s);
        return matcher.matches();
    }

    private File createFile(String fileName) {
        if (StringUtils.isEmpty(fileName)) {
            return null;
        }
        File file = new File(fileName);

        if (!file.exists()) {
            try {
                boolean b = file.createNewFile();
                if (!b) {
                    System.out.println("创建文件失败");
                }
            } catch (IOException e) {
                System.out.println("创建文件失败");
            }
        }

        return file;
    }

    @Test
    public void test() {
        System.out.println(isNumber("5.33"));
    }

    public List<ExcelQaSchemaInfo> readExcel(String fileUrl) {
        InputStream isp = null;
        List<ExcelQaSchemaInfo> list = null;
        try {
            URL url = new URL(fileUrl);
            isp = url.openConnection().getInputStream();
            ExcelHelper<ExcelQaSchemaInfo> helper = ExcelHelper.newInstance(ExcelQaSchemaInfo.class);
            list = helper.parseExcel(isp);
            System.out.println("共有" + list.size() + "条excel数据");
        } catch (IOException e) {
            System.out.println("读取文件失败");
        } finally {
            close(isp);
        }

        return Optional.of(list).orElse(new ArrayList<>(0));
    }

    @Test
    public void checkExcelData() {
        String excelUrl1 = "file:///Users/liuyang/Downloads/qa_scheme_info.xlsx";
        String excelUrl2 = "file:///Users/liuyang/project/qa_sheme_info_bak.xlsx";

        List<ExcelQaSchemaInfo> list1 = readExcel(excelUrl1);
        List<ExcelQaSchemaInfo> list2 = readExcel(excelUrl2);
        Map<String, ExcelQaSchemaInfo> map1 = new HashMap<>();
        String splitter = "-";
        list1.forEach(info -> map1.put(info.getId() + splitter + info.getBasicPrice(), info));
        list2.forEach(excelQaSchemaInfo -> {
            ExcelQaSchemaInfo info = map1.get(excelQaSchemaInfo.getId() + splitter + excelQaSchemaInfo.getBasicPrice());
            if (info == null) {
                System.out.println(excelQaSchemaInfo.getId() + ": 数据紊乱！");
            }
        });

    }
}
